﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;
using Jumpton.Data;
using System.Data;

namespace Jumpton.SFA.Server.BusinessRule.CPR
{
    public class CPR_Data_Manager : BusinessLogic
    {






        /// <summary>
        ///获取最后一条货架库存与最后一配货量之和相关数据
        /// </summary>
        /// <param name="domainID"></param>
        /// <param name="customerID"></param>
        /// <returns></returns>
        public DataTable GetLastCPRTable(string domainID, string userID)
        {

            string sql = @" SELECT P.ID AS PRODUCTID,P.CODE,P.DESCRIPTION AS PRODUCTNAME,C.ID AS CUSTOMERSID,C.DOMAIN_ID,dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar)) AS LASTDATE
,isnull(KC.VALUE,0) AS KCVALUE,isnull(PH.VALUE,0) AS PHVALUE,isnull(TH.VALUE,0) AS THVALUE  
FROM  CUSTOMER_PROD_LIST_ITEMS CP
inner join CUSTOMERS C ON C.customer_product_list_id= CP.customer_product_list_id
inner join  ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=C.ID AND RC.ROUTE_NUMBER=0 AND RC.USER_ID='{0}' AND C.VALID = '1'  AND RC.ACTIVE = '1'
AND C.DOMAIN_ID='{1}'
left JOIN PRODUCTS P ON P.ID=CP.PRODUCT_ID 
LEFT JOIN CPR_HJKC_LAST_TIME KC  ON P.ID=KC.PRODUCTS_ID AND C.ID=KC.CUSTOMERS_ID 
LEFT JOIN CPR_TH_LAST_TIME TH ON TH.CUSTOMERS_ID=KC.CUSTOMERS_ID AND TH.PRODUCTS_ID=KC.PRODUCTS_ID AND C.ID=TH.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(TH.CREATE_TIME as varchar))
LEFT JOIN CPR_SJPH_LAST_TIME PH ON PH.CUSTOMERS_ID=KC.CUSTOMERS_ID AND PH.PRODUCTS_ID=KC.PRODUCTS_ID AND C.ID=PH.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(TH.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(PH.CREATE_TIME as varchar)) order by c.id,P.sequence
";

            sql = string.Format(sql, userID, domainID);

            return SqlData.OpenSql(sql).Tables[0];
        }

        public DataTable GetCPRTable(string domainID, string startdate, string enddate, string where)
        {

            string sql = @"SELECT  C.CODE AS CCODE,C.NAME AS CNAME,P.CODE AS PCODE,P.DESCRIPTION AS PRODUCTNAME
,isnull(KC.VALUE,0) AS KCVALUE,isnull(PH.VALUE,0) AS PHVALUE,isnull(TH.VALUE,0) AS THVALUE,
isnull(OL.QUANTITY_ORDERED,0) AS ORDER_COUNT,dbo.GetDateStringByBigint(cast(V.LAST_UPDATE_TIME as varchar)) as date
FROM VISITS V
inner join CUSTOMERS C ON C.ID=V.CUSTOMER_ID  AND C.DOMAIN_ID='{0}' AND dbo.GetDateStringByBigint(cast(V.LAST_UPDATE_TIME as varchar)) between '{1}' and '{2}'
inner join CUSTOMER_PROD_LIST_ITEMS CP ON C.customer_product_list_id= CP.customer_product_list_id 
inner JOIN PRODUCTS P ON P.ID=CP.PRODUCT_ID 
LEFT JOIN CPR_HJKC KC  ON P.ID=KC.PRODUCTS_ID AND C.ID=KC.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(V.LAST_UPDATE_TIME as varchar))
LEFT JOIN CPR_TH TH ON TH.CUSTOMERS_ID=KC.CUSTOMERS_ID AND TH.PRODUCTS_ID=KC.PRODUCTS_ID AND C.ID=TH.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(TH.CREATE_TIME as varchar))
LEFT JOIN CPR_SJPH PH ON PH.CUSTOMERS_ID=KC.CUSTOMERS_ID AND PH.PRODUCTS_ID=KC.PRODUCTS_ID AND C.ID=PH.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(TH.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(PH.CREATE_TIME as varchar)) 
LEFT JOIN ORDER_HEADERS OH ON OH.CUSTOMER_ID=C.ID AND OH.DOMAIN_ID=C.DOMAIN_ID AND dbo.GetDateStringByBigint(cast(OH.ORDER_DATE as varchar))=dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))
LEFT JOIN ORDER_LINES OL ON OH.ORDER_NO=OL.ORDER_NO AND OL.PRODUCT_ID=P.ID WHERE 1=1 AND V.STATUS=2 {3}
order by V.LAST_UPDATE_TIME desc,c.id,P.sequence
";
            sql = string.Format(sql, domainID, startdate, enddate, where);

            return SqlData.OpenSql(sql).Tables[0];
        }


        /// <summary>
        ///最后一次排面，订货，促销
        /// </summary>
        /// <param name="domainID"></param>
        /// <param name="customerID"></param>
        /// <returns></returns>
        public DataSet GetLastCPRPMTable(string domainID, string userID)
        {
            string weekday = getWeekDay();

            //当天拜访客户
            string sql = @" SELECT C.ID,MAX(OH.ORDER_DATE) AS lAST_ORDER_DATE FROM  CUSTOMERS C 
INNER JOIN  ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=C.ID  AND C.VALID=RC.ACTIVE 
LEFT JOIN ORDER_HEADERS AS OH ON OH.CUSTOMER_ID=C.ID 
 WHERE RC.ROUTE_NUMBER={0} AND RC.USER_ID={1} AND C.VALID = '1'
AND C.DOMAIN_ID={2} GROUP BY C.ID ORDER BY C.ID;
";
            sql = string.Format(sql, weekday, userID, domainID);

            //覆盖产品的系列
            sql += @" SELECT F.CODE,F.NAME,C.ID 
FROM  CUSTOMER_PROD_LIST_ITEMS CP
INNER JOIN CUSTOMERS C ON C.customer_product_list_id= CP.customer_product_list_id
INNER JOIN ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=C.ID  AND C.VALID=RC.ACTIVE
LEFT  JOIN PRODUCTS P ON P.ID=CP.PRODUCT_ID 
INNER JOIN FORMS F ON P.FORM_CODE=F.CODE AND F.DOMAIN_ID=C.DOMAIN_ID
WHERE RC.ROUTE_NUMBER={0} AND RC.USER_ID={1} AND C.VALID = '1'
AND C.DOMAIN_ID={2} GROUP BY F.CODE,F.NAME,C.ID ORDER BY C.ID;
";
            sql = string.Format(sql, weekday, userID, domainID);

            //产品及排面数据
            sql += @"SELECT   C.ID AS CUSTOMERSID,F.NAME,F.CODE AS FORMCODE,P.ID AS PRODUCTID,P.DESCRIPTION AS PRODUCTNAME,P.CODE,P.DUN_CODE,CP.FLAG,C.DOMAIN_ID
,isnull(KC.VALUE,0) AS KCVALUE,isnull(PM.VALUE,0) AS PMVALUE,'' AS DHVALUE,'' AS CXVALUE,CP.CASE_PRICE,CP.PIECE_PRICE
FROM  CUSTOMER_PROD_LIST_ITEMS CP
INNER JOIN CUSTOMERS C ON C.customer_product_list_id= CP.customer_product_list_id
INNER JOIN  ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=C.ID  AND C.VALID=RC.ACTIVE
LEFT  JOIN PRODUCTS P ON P.ID=CP.PRODUCT_ID 
INNER JOIN FORMS F ON P.FORM_CODE=F.CODE AND F.DOMAIN_ID=C.DOMAIN_ID
LEFT JOIN CPR_HJKC_LAST_TIME KC  ON P.ID=KC.PRODUCTS_ID AND C.ID=KC.CUSTOMERS_ID 
LEFT JOIN CPR_PM_LAST_TIME PM ON PM.CUSTOMERS_ID=KC.CUSTOMERS_ID AND PM.PRODUCTS_ID=KC.PRODUCTS_ID AND C.ID=PM.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(PM.CREATE_TIME as varchar))
WHERE RC.ROUTE_NUMBER={0} AND RC.USER_ID={1} AND C.VALID = '1'
AND C.DOMAIN_ID={2}  ORDER BY C.ID,P.SEQUENCE;";

            sql = string.Format(sql, weekday, userID, domainID);

            return SqlData.OpenSql(sql);
        }

        /// <summary>
        ///获取价格套
        /// </summary>
        /// <param name="domainID"></param>
        /// <param name="customerID"></param>
        /// <returns></returns>
        public DataSet GetProductPriceListNew(string domainID, string userID)
        {
            //覆盖产品的系列
            string sql = @" SELECT DISTINCT CPLI.ID,CPLI.CUSTOMER_PRODUCT_LIST_ID,CPLI.PRODUCT_ID,CPLI.CASE_PRICE,CPLI.ACTIVE 
FROM [CUSTOMER_PROD_LIST_ITEMS] CPLI
INNER JOIN CUSTOMERS C ON 
C.customer_product_list_id= CPLI.customer_product_list_id 
INNER JOIN ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=C.ID
WHERE RC.ROUTE_NUMBER=0 AND C.DOMAIN_ID={0} AND RC.USER_ID={1}  ORDER BY CPLI.CUSTOMER_PRODUCT_LIST_ID,
CPLI.PRODUCT_ID";
            sql = string.Format(sql, domainID, userID);
            return SqlData.OpenSql(sql);
        }

        /// <summary>
        ///最后一次排面，订货，促销
        /// </summary>
        /// <param name="domainID"></param>
        /// <param name="customerID"></param>
        /// <returns></returns>
        public DataSet GetLastCPRPMTableNew(string domainID, string userID)
        {

            //产品及排面数据
            string sql = @"SELECT t1.CUSTOMERS_ID,t1.PRODUCTS_ID,t1.VALUE AS KCVALUE,t2.VALUE AS PMVALUE FROM
(
select KC.CUSTOMERS_ID,KC.PRODUCTS_ID,KC.VALUE from CPR_KC_LAST_TIME  KC inner join ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=KC.CUSTOMERS_ID 
where kc.DOMAIN_ID={0} AND RC.USER_ID={1} AND RC.ROUTE_NUMBER=0
) t1,
(
select PM.CUSTOMERS_ID,PM.PRODUCTS_ID,PM.VALUE from CPR_KC_LAST_TIME  PM inner join ROUTE_CUSTOMERS RC ON RC.CUSTOMER_ID=PM.CUSTOMERS_ID
where pm.DOMAIN_ID={2} AND RC.USER_ID={3} AND RC.ROUTE_NUMBER=0) t2
where t1.CUSTOMERS_ID=t2.CUSTOMERS_ID AND t1.PRODUCTS_ID=t2.PRODUCTS_ID ;";

            sql = string.Format(sql, domainID, userID, domainID, userID);

            return SqlData.OpenSql(sql);
        }

        /// <summary>
        /// 排面，订货，促销
        /// </summary>
        /// <param name="domainID"></param>
        /// <param name="startdate"></param>
        /// <param name="enddate"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetCPRPMTable(string domainID, string userid, string startdate, string enddate, string where)
        {

            string sql = @"SELECT  C.CODE AS CCODE,C.NAME AS CNAME,P.CODE AS PCODE,P.DESCRIPTION AS PRODUCTNAME
,isnull(KC.VALUE,0) AS KCVALUE,isnull(PM.VALUE,0) AS PMVALUE,'' AS DHVALUE,'' AS CXVALUE,CP.CASE_PRICE,CP.PIECE_PRICE
,dbo.GetDateStringByBigint(cast(V.LAST_UPDATE_TIME as varchar)) as date
FROM VISITS V
inner join CUSTOMERS C ON C.ID=V.CUSTOMER_ID   AND  dbo.[GetDateTimeByBigint](V.LAST_UPDATE_TIME) between dbo.GetDateTimeByBigint({0}000000) and dbo.GetDateTimeByBigint({1}235959)
inner join CUSTOMER_PROD_LIST_ITEMS CP ON C.customer_product_list_id= CP.customer_product_list_id 
inner JOIN PRODUCTS P ON P.ID=CP.PRODUCT_ID 
LEFT JOIN CPR_HJKC KC  ON P.ID=KC.PRODUCTS_ID AND C.ID=KC.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(V.LAST_UPDATE_TIME as varchar))
LEFT JOIN CPR_PM PM ON PM.CUSTOMERS_ID=KC.CUSTOMERS_ID AND PM.PRODUCTS_ID=KC.PRODUCTS_ID AND C.ID=PM.CUSTOMERS_ID AND dbo.GetDateStringByBigint(cast(KC.CREATE_TIME as varchar))=dbo.GetDateStringByBigint(cast(PM.CREATE_TIME as varchar))
WHERE 1=1 AND V.USER_ID={2} AND C.DOMAIN_ID={3} {4}
order by V.LAST_UPDATE_TIME desc,c.id,P.sequence
";
            sql = string.Format(sql, startdate, enddate, userid, domainID, where);


            return SqlData.OpenSql(sql).Tables[0];
        }



        /// <summary>
        /// 获取CPR_BRAND_DATAS
        /// </summary>
        /// <returns></returns>
        public DataTable GetCPR_BRAND_Table(string customerid, string visitid,string domainID,string active ,string strWhere)
        {
            string sql = @"select d.NAME,c.* from (
select b.MEASUREMENTNAME,b.SEQUENCE,b.ACTIVE,b.INPUTTYPE,
a.ID,BRAND_CONFIG_ID,a.CUSTOMERS_ID,a.BRAND_ID,a.VISITID,a.VALUE,a.VALUE_TYPE,a.PHOTO_ID,a.DOMAIN_ID, 
a.LAST_UPDATER,a.LAST_UPDATE_TIME,a.CREATE_TIME
from 
(select  * from CPR_BRAND_DATAS) a,
(select * from CPR_BRAND_CONFIG ) b
where a.BRAND_CONFIG_ID = b.ID
) c inner join BRANDS d
on c.BRAND_ID = d.ID
and c.CUSTOMERS_ID='{0}' and c.VISITID='{1}' and c.DOMAIN_ID={2} and c.ACTIVE={3} {4} order by c.SEQUENCE
";
            sql = string.Format(sql, customerid,visitid, domainID,active,strWhere);

            return SqlData.OpenSql(sql).Tables[0];
        }

        /// <summary>
        /// 获取CPR_BRAND_DATAS
        /// </summary>
        /// <returns></returns>
        public DataTable GetCPR_BRAND_Table(string visitid, string domainID, string active, string strWhere)
        {
            string sql = @"select d.NAME,c.* from (
select b.MEASUREMENTNAME,b.SEQUENCE,b.ACTIVE,b.INPUTTYPE,
a.ID,BRAND_CONFIG_ID,a.CUSTOMERS_ID,a.BRAND_ID,a.VISITID,a.VALUE,a.VALUE_TYPE,a.PHOTO_ID,a.DOMAIN_ID, 
a.LAST_UPDATER,a.LAST_UPDATE_TIME,a.CREATE_TIME
from 
(select  * from CPR_BRAND_DATAS) a,
(select * from CPR_BRAND_CONFIG ) b
where a.BRAND_CONFIG_ID = b.ID
) c inner join BRANDS d
on c.BRAND_ID = d.ID
and  c.VISITID='{0}' and c.DOMAIN_ID={1} and c.ACTIVE={2} {3} order by  d.NAME,c.SEQUENCE
";
            sql = string.Format(sql, visitid, domainID, active, strWhere);

            return SqlData.OpenSql(sql).Tables[0];
        }

        public CPR_DATA_CONFIG Load(int id)
        {
            return (CPR_DATA_CONFIG)ObjectRow.Load(typeof(CPR_DATA_CONFIG), string.Format("this.ID='{0}'", id));
        }

        public CPR_DATA_CONFIG Load(decimal domain_id, string code)
        {
            return (CPR_DATA_CONFIG)ObjectRow.Load(typeof(CPR_DATA_CONFIG), string.Format("this.DomainID='{0}' and this.CODE='{1}'", domain_id, code));
        }

        public bool Save(CPR_DATA_CONFIG entity)
        {
            try
            {

                if (entity.State == DataRowState.Added)
                {
                    //entity.CREATE_TIME = long.Parse(BusinessLogic.GetDateTimeString(DateTime.Now));
                }
                entity.Save();

                return true;
            }
            catch (Exception ex)
            {
                entity.Connection.RollbackTransaction();
                this.message = ex.Message;
                throw ex;
            }
        }

       

        public ObjectTable GetCPRConfig(string domainID)
        {

            return new ObjectTable(typeof(CPR_DATA_CONFIG), string.Format("DOMAIN_ID={0}", domainID));
        }


        public ObjectTable GetCPRConfig(string domainID, string active)
        {

            return new ObjectTable(typeof(CPR_DATA_CONFIG), string.Format("DOMAIN_ID={0} AND ACTIVE='{1}'", domainID, active));
        }

        /// <summary>
        /// 从最后一张表中删除对应数据
        /// </summary>
        public void DeleteLastData(string customerID, string tablename, decimal domainID)
        {
            try
            {
                string sql = @" delete from {0}  where  CUSTOMERS_ID={1}  AND DOMAIN_ID={2}";

                sql = string.Format(sql, tablename, customerID, domainID);
                SqlData.OpenSql(sql);
            }
            catch (Exception ex) { throw ex; }

        }

        /// <summary>
        /// 从最后一张表中插入对应数据
        /// </summary>
        public void InsertCPRData(string customerID, string procuctID, string value, string vid, string tablename, decimal domainID, string userid)
        {
            string sql = "";
            try
            {
                sql = @" insert into  {4} (CUSTOMERS_ID, PRODUCTS_ID, VALUE, VISITID, ACTIVE, DOMAIN_ID, LAST_UPDATER, LAST_UPDATE_TIME, CREATE_TIME) values({5},{6},{7},{8},'{9}',{10},'{11}',{12},{13}) ";
                decimal time = BusinessLogic.GetDateTimeDecimal(DateTime.Now);
                sql = string.Format(sql, tablename, customerID, procuctID, domainID, tablename, customerID, procuctID, value, vid, "1", domainID, userid, time, time);
                SqlData.OpenSql(sql);
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + tablename + " " + sql);
            }

        }

        /// <summary>
        /// 从最后一张表中插入对应数据
        /// </summary>
        public void InsertLastCPRData(string customerID, string procuctID, string value, string vid, string tablename, decimal domainID, string userid)
        {
            string sql = "";
            try
            {
                sql = @" delete from {0} where CUSTOMERS_ID={1} AND PRODUCTS_ID={2} AND DOMAIN_ID={3}; insert into  {4} (CUSTOMERS_ID, PRODUCTS_ID, VALUE, VISITID, ACTIVE, DOMAIN_ID, LAST_UPDATER, LAST_UPDATE_TIME, CREATE_TIME) values({5},{6},{7},{8},'{9}',{10},'{11}',{12},{13}) ";
                decimal time = BusinessLogic.GetDateTimeDecimal(DateTime.Now);
                sql = string.Format(sql, tablename, customerID, procuctID, domainID, tablename, customerID, procuctID, value, vid, "1", domainID, userid, time, time);
                SqlData.OpenSql(sql);
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + tablename + " " + sql);
            }

        }


    }
}
